/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package org.cheetah.sisal;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Time;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.cheetah.sisal.obj.MergedCell;
import org.cheetah.sisal.obj.XlsSheet;
import org.cheetah.sisal.sql.Database;

/**
 *
 * @author edoardo
 */
public class Xls {

    static List<XlsRow> xlsRows = new ArrayList<XlsRow>();
    static HashSet<XlsRow> superHeaders = new HashSet<XlsRow>();
    static HashSet<XlsRow> headers = new HashSet<XlsRow>();

    static HashSet<XlsMergeRange> mergeRanges = new HashSet<XlsMergeRange>();

    private Xls() {
    }

    public static void store(Connection con, FileInputStream in) throws Exception {
        try {
            String sql = "select * from VIEW_GIOCATE ";
            PreparedStatement pstmt = con.prepareStatement(sql);
            //converte la descrizione in identificativo
            Map<String, Integer> descToIdTipoGiocataIdMap = new HashMap<String, Integer>();
            Map<String, Integer> descToIdSegnoMap = new HashMap<String, Integer>();
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                if (!descToIdTipoGiocataIdMap.containsKey(rs.getString("TIPO_GIOCATA"))) {
                    descToIdTipoGiocataIdMap.put(rs.getString("TIPO_GIOCATA"), rs.getInt("ID_TIPO_GIOCATA"));
                }
            }
            rs.close();
            pstmt.clearBatch();
            //leggo il file

            Workbook wb = WorkbookFactory.create(in);
            Sheet sheet = wb.getSheetAt(0);
            //prendo la prima riga con le intestazioni principali delle possibili giocate (es. ESITO FINALE 1X2)
            Row priHeaders = sheet.getRow(0);
            Row headerSegni = sheet.getRow(1);
            int counter = 0;
            Map<String, CellRange> headersColumnIdxMap = new HashMap<String, CellRange>();
            int numMergedRegions = sheet.getNumMergedRegions();
            for (int i = 0; i < numMergedRegions;
                    i++) {
                CellRangeAddress cra = sheet.getMergedRegion(i);
                if (cra.getFirstRow() == priHeaders.getRowNum()) {
                    CellRange cr = new CellRange(cra.getFirstColumn(), cra.getLastColumn());
                    headersColumnIdxMap.put(priHeaders.getCell(cra.getFirstColumn()).getStringCellValue(), cr);
                    continue;
                }

            }
            System.out.println(headersColumnIdxMap);
//        for (Iterator<Cell> it = priHeaders.iterator(); it.hasNext();) {
//            Cell cell = it.next();
//            if (descToIdTipoGiocataIdMap.containsKey(cell.getStringCellValue())) {
//                headersColumnIdxMap.put(counter,cell.getStringCellValue());
//            }
//
//        }
            //primo ciclo per dividere le righe per palinsesto.
            Map<String, List<Row>> rowsByPalinsesto = new HashMap<String, List<Row>>();

            Iterator<Row> rows = sheet.iterator();
            int firstRow = 0;
            while (rows.hasNext()) {
                Row row = rows.next();

                if (row.getCell(0).getStringCellValue().toLowerCase().indexOf("pal.") != -1) {
                    firstRow = row.getRowNum();
                    break;
                }
            }
            System.out.println("FirstRow: " + firstRow);
            Iterator<Row> itRows = sheet.iterator();
            String pal = "";
            int r = 0;
            rowIteratorLabel:
            for (; itRows.hasNext(); r++) {
                Row startingRow = itRows.next();
                if (r >= 2) {
                    Iterator<Cell> cells = startingRow.iterator();
                    while (cells.hasNext()) {
                        Cell cell = cells.next();
                        if (cell.getStringCellValue().toLowerCase().indexOf("pal.") != -1) {
                            pal = cell.getStringCellValue();

                            continue rowIteratorLabel;
                        }
                    }
                    if (!pal.equals("")) {
                        List<Row> _rows = rowsByPalinsesto.get(pal);
                        if (_rows == null) {
                            _rows = new ArrayList<Row>();
                        }
                        _rows.add(startingRow);
                        rowsByPalinsesto.put(pal, _rows);
                    }
                }
            }
//        for (Map.Entry<String, List<Row>> entry : rowsByPalinsesto.entrySet()) {
//            String string = entry.getKey();
//            List<Row> list = entry.getValue();
//            System.out.println(string + ": " + list.size());
//            for (Iterator<Row> it = list.iterator(); it.hasNext();) {
//                Row row = it.next();
//                System.out.println("\t" + row.getCell(3));
//            }
//        }
            for (Map.Entry<String, List<Row>> entry : rowsByPalinsesto.entrySet()) {
                String palinsesto = entry.getKey();
                List<Row> list = entry.getValue();
                //ora prendo ogni singola riga e la inserisco su db
                for (Iterator<Row> it = list.iterator(); it.hasNext();) {
                    Row row = it.next();
                    //conoscende già le celle con data e avvenimento faccio che prenderle direttamente
                    String dateTime = row.getCell(0).getStringCellValue();
                    Calendar calendar = Calendar.getInstance();
                    String date = dateTime.substring(0, dateTime.indexOf(" "));
                    String time = dateTime.substring(dateTime.indexOf(" ")).trim();
                    date = date + "-" + calendar.get(Calendar.YEAR);
                    SimpleDateFormat sdf = (SimpleDateFormat) SimpleDateFormat.getDateInstance();
                    SimpleDateFormat sdft = (SimpleDateFormat) SimpleDateFormat.getTimeInstance();
                    sdf.applyLocalizedPattern("dd-MMM-yyyy");
                    sdft.applyLocalizedPattern("HH:mm");
                    java.sql.Date dataAvvenimento = new java.sql.Date(sdf.parse(date).getTime());
                    Time oraAvvenimento = new Time(sdft.parse(time).getTime());

                    String numAvvenimento = row.getCell(3).getStringCellValue();
                    String avvenimento = row.getCell(4).getStringCellValue();
                    try {
                        String insertPalinsesti = Database.getSql("database.insert.palinsesti");
                        con.setAutoCommit(false);
                        pstmt = con.prepareStatement(insertPalinsesti);
                        pstmt.setInt(1, Integer.parseInt(palinsesto.substring(palinsesto.indexOf(".") + 1)));
                        pstmt.setString(2, numAvvenimento);
                        pstmt.setString(3, avvenimento);
                        pstmt.setDate(4, dataAvvenimento);
                        pstmt.setTime(5, oraAvvenimento);
                        pstmt.setDate(6, new java.sql.Date(System.currentTimeMillis()));
                        pstmt.execute();
                        con.commit();
                        pstmt.close();

                    } catch (Exception e) {
                        e.printStackTrace();
                        con.rollback();
                        throw e;
                    }
                    //prelevo le colonne con le quote per ciacun riga
                    for (Map.Entry<String, CellRange> entry1 : headersColumnIdxMap.entrySet()) {
                        String itTipoGiocata = entry1.getKey();
                        CellRange cr = entry1.getValue();
                        Cell cellQuota = null;
                        Cell cellIntestazione = null;
                        if (cr.getStart() == cr.getEnd()) {
                            cellQuota = row.getCell(cr.getStart());
                            cellIntestazione = headerSegni.getCell(cr.getStart());
                            if (cellQuota != null && cellIntestazione != null) {
                                System.out.println(cellIntestazione.getStringCellValue() + ": " + cellQuota.getStringCellValue());
                            }
                        } else {
                            int start = cr.getStart();
                            int end = cr.getEnd();
                            for (counter = start; counter <= end; counter++) {
                                cellQuota = row.getCell(counter);
                                cellIntestazione = headerSegni.getCell(counter);
                                if (cellQuota != null && cellIntestazione != null) {
                                    if (descToIdTipoGiocataIdMap.containsKey(priHeaders.getCell(start).getStringCellValue())) {
                                        int idTipoGiocata = descToIdTipoGiocataIdMap.get(priHeaders.getCell(start).getStringCellValue());
                                        sql = Database.getSql("database.findGiocateByIdTipoGiocataAndSegno.viewGiocate");
                                        pstmt = con.prepareStatement(sql);
                                        pstmt.setInt(1, idTipoGiocata);
                                        pstmt.setString(2, cellIntestazione.getStringCellValue());
                                        rs = pstmt.executeQuery();
                                        int idValoreTipoGiocata = -1;

                                        if (rs.next()) {
                                            idValoreTipoGiocata = rs.getInt("ID_VALORE_TIPO_GIOCATA");
//                                            System.out.println(priHeaders.getCell(start).getStringCellValue() + "-" + cellIntestazione.getStringCellValue() + " :" + idValoreTipoGiocata);
                                        }
                                        System.out.println(palinsesto + "-" + numAvvenimento);
                                        System.out.println("\t" + priHeaders.getCell(start).getStringCellValue() + ": " + idTipoGiocata + "/" + idValoreTipoGiocata + " - " + cellIntestazione.getStringCellValue());

                                        pstmt.close();
                                        rs.close();
                                        try {
                                            String insertQuote = Database.getSql("database.insert.quote");
                                            con.setAutoCommit(false);
                                            if (row.getCell(start) != null) {
                                                String quota = row.getCell(counter).getStringCellValue();

                                                if (quota.indexOf("(") != -1 && quota.indexOf(")") != -1) {
                                                    quota = quota.substring(0, quota.indexOf("(")).trim();
                                                }
                                                pstmt = con.prepareStatement(insertQuote);
                                                pstmt.setString(1, RandomStringUtils.randomAlphanumeric(100));
                                                pstmt.setDouble(2, Double.parseDouble(quota));
                                                pstmt.setInt(3, Integer.parseInt(palinsesto.substring(palinsesto.indexOf(".") + 1)));
                                                pstmt.setString(4, numAvvenimento);
                                                pstmt.setInt(5, idValoreTipoGiocata);

                                                pstmt.execute();
                                                con.commit();
                                                pstmt.close();
                                            }
                                        } catch (Exception e) {
                                            e.printStackTrace();
                                            con.rollback();
                                            con.close();
                                            throw e;
                                        }
                                    }
//                                    System.out.println(idTipoGiocata + "/" + palinsesto + "/" + numAvvenimento + ":" + cellIntestazione.getStringCellValue() + ": " + cellQuota.getStringCellValue());
                                }
                            }
                        }
                    }
                }

//                System.out.println(palinsesto+": "+numAvvenimento+", "+avvenimento);
            }
        } finally {
            if (con != null && !con.isClosed()) {
                con.close();
            }
        }
    }

    public static Xls getInstance() throws IOException, InvalidFormatException {
        Workbook wb = WorkbookFactory.create(new ByteArrayInputStream(CreateBet.xls.get()));
        Sheet sheet = wb.getSheetAt(0);
        int numMergedRegion = sheet.getNumMergedRegions();
        for (int i = 0; i < numMergedRegion; i++) {
            CellRangeAddress cra = sheet.getMergedRegion(i);
            mergeRanges.add(new XlsMergeRange(cra));
        }
        int r = 0;
        boolean superHeader = false;
        boolean header = false;
        for (Iterator<Row> it = sheet.rowIterator(); it.hasNext(); r++) {
            Row row = it.next();
            Xls.XlsRow xlsRow = new Xls.XlsRow(row);
            xlsRow.rownum = r;
            int c = 0;
            for (Iterator<Cell> itCell = row.cellIterator(); itCell.hasNext(); c++) {
                Cell cell = itCell.next();
                String value = cell.getStringCellValue();
                if (value.equalsIgnoreCase("calcio")) {
                    XlsColumn xlsColumn = new XlsColumn(cell);
//                    xlsColumn.descrizione = value;
                    xlsRow.addColumn(xlsColumn);
//                    System.out.println(c + ": " + cell.getStringCellValue());
                    superHeader = true;
                    for (; itCell.hasNext(); c++) {
                        cell = itCell.next();
                        if (cell.getStringCellValue() != null && cell.getStringCellValue().indexOf(",") != -1) {
                            cell.setCellValue(cell.getStringCellValue().replace(",", "."));
                        }
                        XlsColumn temp = new XlsColumn(cell);
//                        temp.descrizione = cell.getStringCellValue();
                        if (!cell.getStringCellValue().trim().equals("")) {
//                            System.out.println(c + ": " + cell.getStringCellValue());
                            xlsRow.addColumn(temp);
                        }

                    }
                    superHeaders.add(xlsRow);
                }
                if (value.equalsIgnoreCase("data ora")) {
//                    System.out.println("-----------------------------");
//                    System.out.println(c + ": " + cell.getStringCellValue());
                    header = true;
                    XlsColumn xlsColumn = new XlsColumn(cell);
//                    xlsColumn.descrizione = value;
                    xlsRow.addColumn(xlsColumn);
                    for (; itCell.hasNext(); c++) {
                        cell = itCell.next();
                        XlsColumn temp = new XlsColumn(cell);
//                        temp.descrizione = cell.getStringCellValue();
                        if (!cell.getStringCellValue().trim().equals("")) {
//                            System.out.println(c + ": " + cell.getStringCellValue());
                            xlsRow.addColumn(temp);
                        }

                    }
                    headers.add(xlsRow);
                }
                if (xlsRow.columns.size() > 0) {
                    System.out.println(xlsRow);
                }
            }
            String palinsesto = "";
            String competizione = "";
            if (header) {
                pronostici:
                for (; it.hasNext(); r++) {
                    row = it.next();
                    XlsRow _xlsRow = new XlsRow(row);
                    _xlsRow.rownum = r;
                    c = 0;
                    for (Iterator<Cell> itCell = row.cellIterator(); itCell.hasNext();) {
                        Cell cell = itCell.next();

                        XlsColumn xlsColumn = new XlsColumn(cell);

                        if (cell.getStringCellValue().toLowerCase().indexOf("pal.") != -1) {
//                            System.out.println("=====> "+cell.getStringCellValue());
                            palinsesto = cell.getStringCellValue();
//                            xlsColumn.descrizione = palinsesto;
                            _xlsRow.addColumn(xlsColumn);
                            Cell _cell = row.getCell(3);//itCell.next();
//                            System.out.println(_cell.getStringCellValue()+"<=====");
                            XlsColumn _xlsColumn = new XlsColumn(_cell);

                            competizione = _cell.getStringCellValue();
//                            _xlsColumn.descrizione = competizione;
                            _xlsRow.addColumn(_xlsColumn);
//                            System.out.println(xlsColumn + ": " + _xlsColumn);
                            xlsRows.add(_xlsRow);
//                            System.out.println("\t"+_xlsRow);
                            continue pronostici;
                        }
                        if (!cell.getStringCellValue().trim().equals("")) {
//                            xlsColumn.descrizione = cell.getStringCellValue();
                            _xlsRow.addColumn(xlsColumn);
//                            System.out.println("\t" +xlsColumn);
                        }
                    }
                    if (_xlsRow.columns.size() > 0) {
                        if (!superHeaders.contains(_xlsRow) && !headers.contains(_xlsRow)) {
                            xlsRows.add(_xlsRow);
                            System.out.println("\t\t" + _xlsRow);
                        }
                    }
                }
            }
        }
//        System.out.println("---------------------------------------------------------------------");
//        System.out.println(superHeaders);
//        System.out.println(headers);
//        System.out.println(xlsRows);
//        System.out.println("---------------------------------------------------------------------");
        return new Xls();
    }

    public void write() throws IOException, InvalidFormatException {
        StringBuilder nomeFile = new StringBuilder("sisal.");

        nomeFile.append(Xls.getDate("yyyyMMdd")).append(".xls");
        nomeFile = new StringBuilder(System.getProperty("user.home")).append(File.separator).append(nomeFile.toString());
        FileOutputStream out = new FileOutputStream(nomeFile.toString());

        Workbook wb = new HSSFWorkbook();

        Sheet sheet = wb.createSheet("PARDO");
        int r = 0;
        for (XlsRow row : superHeaders) {
            row.writeRow(sheet, r);
            r++;
        }
        for (XlsRow row : headers) {
            row.writeRow(sheet, r);
            r++;
        }
        for (XlsRow row : xlsRows) {
            row.writeRow(sheet, r);
            r++;
        }
        int idxXColumn = 0;
        int idx1Column = 0;
        int idx2Column = 0;
        int idx1XColumn = 0;
        int idx12Column = 0;
        int idxX2Column = 0;
        //prelevo la riga con gli header delle colonne delle quote
        Row row = sheet.getRow(1);
        for (Iterator<Cell> it = row.iterator(); it.hasNext();) {
            Cell _cell = it.next();
            if (_cell.getStringCellValue().equalsIgnoreCase("x") && idxXColumn == 0) {
                idxXColumn = _cell.getColumnIndex();
            }
            if (_cell.getStringCellValue().equals("1") && idx1Column == 0) {
                idx1Column = _cell.getColumnIndex();

            }
            if (_cell.getStringCellValue().equals("2") && idx2Column == 0) {
                idx2Column = _cell.getColumnIndex();

            }
            if (_cell.getStringCellValue().equalsIgnoreCase("1x")) {
                idx1XColumn = _cell.getColumnIndex();

            }
            if (_cell.getStringCellValue().equalsIgnoreCase("x2")) {
                idxX2Column = _cell.getColumnIndex();

            }
            if (_cell.getStringCellValue().equalsIgnoreCase("12")) {
                idx12Column = _cell.getColumnIndex();

            }
        }
        r = 0;
        String palinsesto = "";
        for (Iterator<Row> it = sheet.iterator(); it.hasNext(); r++) {
            Row row1 = it.next();
            if (row1.getCell(0).getStringCellValue().toLowerCase().indexOf("pal.") != -1) {
                palinsesto = row1.getCell(0).getStringCellValue();
                System.out.println(palinsesto);
            }
            if (r > 2) {
                Cell vittoriaCasa = null;
                Cell vittoriaFuori = null;
                Cell pareggio = null;
                Cell vittoria1X = null;
                Cell vittoriaX2 = null;
                Cell vittoria12 = null;
                for (Iterator<Cell> it1 = row1.iterator(); it1.hasNext();) {
                    Cell cell = it1.next();
                    if (cell.getColumnIndex() == idx1Column) {
                        vittoriaCasa = cell;
                    }
                    if (cell.getColumnIndex() == idx1XColumn) {
                        vittoria1X = cell;
                    }
                    if (cell.getColumnIndex() == idx12Column) {
                        vittoria12 = cell;
                    }
                    if (cell.getColumnIndex() == idxX2Column) {
                        vittoriaX2 = cell;
                    }
                    if (cell.getColumnIndex() == idxXColumn) {
                        pareggio = cell;
                    }
                    if (cell.getColumnIndex() == idx2Column) {
                        vittoriaFuori = cell;
                    }

                }
                calcola(wb, vittoriaCasa, pareggio, vittoriaFuori, vittoria1X, vittoriaX2, vittoria12);
            }
        }
        wb.write(out);

        out.close();
        //creo il file della bolletta
        Workbook bolletta = new HSSFWorkbook();
        Sheet newSheet = bolletta.createSheet("Bolletta");
        wb = WorkbookFactory.create(new FileInputStream(nomeFile.toString()));
        r = 1;
        Sheet sisalExported = wb.getSheetAt(0);
        Row rowIntestazione = null;
        Row rowPalinsesto = null;
        int totalColumns = 0;
        Row newIntestazione = newSheet.createRow(0);
        CellStyle style = bolletta.createCellStyle();
        Font boldFont = bolletta.createFont();
        boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(boldFont);
        createCell(newIntestazione, "Palinsesto", 0, style);
        createCell(newIntestazione, "Data", 1, style);
        createCell(newIntestazione, "Avvenimento", 2, style);
        createCell(newIntestazione, "Match", 3, style);
        createCell(newIntestazione, "Pronostico", 5, style);
        createCell(newIntestazione, "Quota", 6, style);

        rows:
        for (Iterator<Row> it = sisalExported.iterator(); it.hasNext();) {
            Row rowSisalExported = it.next();
            if (rowSisalExported.getCell(0).getStringCellValue().equalsIgnoreCase("data ora")) {
                rowIntestazione = rowSisalExported;
                continue rows;
            }
            if (rowSisalExported.getCell(0).getStringCellValue().toLowerCase().indexOf("pal.") != -1) {
                rowPalinsesto = rowSisalExported;
                continue rows;
            }
            for (Iterator<Cell> it1 = rowSisalExported.iterator(); it1.hasNext();) {
                Cell cellSisalExported = it1.next();
                CellStyle styleSisalExported = cellSisalExported.getCellStyle();
                if (styleSisalExported != null) {
                    if (styleSisalExported.getFillPattern() == 1) {
                        Row newRow = newSheet.createRow(r);
                        r++;
                        Cell celPal = newRow.createCell(0);
                        celPal.setCellValue(rowPalinsesto.getCell(0).getStringCellValue());
                        //itero nuovamente sulla riga letta
                        int c = 1;
                        for (Iterator<Cell> it2 = rowSisalExported.iterator(); it2.hasNext();) {
                            Cell cell1 = it2.next();
                            if (c < 4) {
                                Cell newCell = newRow.createCell(c);
                                newCell.setCellValue(cell1.getStringCellValue());

                                c++;
                            }
                            if (cell1.getColumnIndex() == cellSisalExported.getColumnIndex()) {
                                //mi trovo sulla cella desiderata
                                Cell newCell = newRow.createCell(++c);
                                newCell.setCellValue(rowIntestazione.getCell(cellSisalExported.getColumnIndex()).getStringCellValue());
                                Cell newCell2 = newRow.createCell(++c);
                                newCell2.setCellValue(cellSisalExported.getStringCellValue().substring(0, cellSisalExported.getStringCellValue().indexOf("(")).replace(".", ","));
                                Cell newCell3 = newRow.createCell(++c);
                                newCell3.setCellValue(cellSisalExported.getStringCellValue().substring(cellSisalExported.getStringCellValue().indexOf("(")));

                                totalColumns = c + 3;
                            }
                        }
                    }
                }
            }

        }
        for (int i = 0; i < totalColumns; i++) {
            newSheet.autoSizeColumn(i);
        }

        out = new FileOutputStream(System.getProperty("user.home") + File.separator + "bolletta." + getDate("yyyyMMdd") + ".xls");
        bolletta.write(out);
        out.close();
    }

    private Cell createCell(Row newIntestazione, String content, int idx, CellStyle style) {
        Cell cell = newIntestazione.createCell(idx);
        cell.setCellValue(content);
        if (style != null) {
            cell.setCellStyle(style);
        }
        return cell;
    }

    private Cell createCell(Row newIntestazione, String content, int idx) {
        return createCell(newIntestazione, content, idx, null);
    }

    private void calcola(Workbook wb, Cell vittoriaCasa, Cell pareggio, Cell vittoriaFuori, Cell vittoria1X, Cell vittoriaX2, Cell vittoria12) throws FileNotFoundException {

        if (vittoriaCasa != null && vittoriaFuori != null) {
            double quotaCasa = Double.parseDouble(vittoriaCasa.getStringCellValue());
            double quotaFuori = Double.parseDouble(vittoriaFuori.getStringCellValue());

            double result = (double) (quotaFuori / quotaCasa);

            CellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(IndexedColors.GREEN.getIndex());;
            style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());;
            style.setFillPattern((short) 1);
            DecimalFormat df = (DecimalFormat) DecimalFormat.getNumberInstance();
            df.applyPattern("#.###");

            BigDecimal bdResult = new BigDecimal(String.valueOf(result));
            bdResult.setScale(2, BigDecimal.ROUND_HALF_UP);//, RoundingMode.HALF_UP);
            String value = df.format(result);
            value = value.startsWith(",") || value.startsWith(".") ? "0" + value : value;
            System.out.println("\t" + bdResult + ": " + value);
            if (result > 0.40 && result < 0.90) {
                vittoriaFuori.setCellStyle(style);
                vittoriaFuori.setCellValue(vittoriaFuori.getStringCellValue() + "(" + value + ")");
            }
            if (result > 0.91 && result < 1.10) {
                pareggio.setCellStyle(style);
                pareggio.setCellValue(pareggio.getStringCellValue() + "(" + value + ")");
            }
            if (result > 1.11 && result < 1.55) {
                vittoriaX2.setCellStyle(style);
                vittoriaX2.setCellValue(vittoriaX2.getStringCellValue() + "(" + value + ")");
            }
            if (result > 1.56 && result < 2) {
                vittoria1X.setCellStyle(style);
                vittoria1X.setCellValue(vittoria1X.getStringCellValue() + "(" + value + ")");
            }
            if (result > 2.01 && result < 3) {
                vittoriaCasa.setCellStyle(style);
                vittoriaCasa.setCellValue(vittoriaCasa.getStringCellValue() + "(" + value + ")");
            }
        }
    }

    public static XlsSheet[] getSheet(InputStream xlsIn) throws IOException, InvalidFormatException {

        Workbook wb = WorkbookFactory.create(xlsIn);
        List<XlsSheet> sheets = new ArrayList<XlsSheet>();
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {
            Sheet sheet = wb.getSheetAt(s);
            XlsSheet xlsSheet = new XlsSheet(sheet.getSheetName());
            int mergedRegions = sheet.getNumMergedRegions();
            TreeSet<MergedCell> treeSet = new TreeSet<MergedCell>(new Comparator<MergedCell>() {

                public int compare(MergedCell o1, MergedCell o2) {
                    int r = o1.getFirstRow() - o2.getFirstRow();
                    int c = o1.getFirstColumn() - o2.getFirstColumn();
                    return r == 0 ? c : r;
                }
            });
            for (int m = 0; m < mergedRegions; m++) {
                CellRangeAddress cra = sheet.getMergedRegion(m);
                MergedCell mc = new MergedCell(cra.getFirstColumn(), cra.getFirstRow());
                mc.setFirstColumn(cra.getFirstColumn());
                mc.setLastColumn(cra.getLastColumn());
                mc.setFirstRow(cra.getFirstRow());
                mc.setLastRow(cra.getLastRow());
                treeSet.add(mc);
            }
            for (MergedCell mergedCell : treeSet) {

                System.out.println(mergedCell);
            }
//            System.out.println(treeSet);
            for (Iterator<Row> it = sheet.iterator(); it.hasNext();) {
                Row row = it.next();
//                MergedCell mc = 

            }
        }

        return null;
    }

    public static String getDate(String pattern) {
        Date date = new Date(System.currentTimeMillis());
        SimpleDateFormat sdf = (SimpleDateFormat) SimpleDateFormat.getDateInstance();
        sdf.applyPattern(pattern);
        return sdf.format(date);

    }

    private static class XlsRow {

        private Row row;

        public XlsRow(Row row) {
            this.row = row;
        }

        int rownum;
        List<XlsColumn> columns = new ArrayList<XlsColumn>();

        public XlsRow addColumn(XlsColumn col) {
            if (!col.descrizione.equals("PALINSESTO CALCIO")) {
                columns.add(col);
            }
            return this;
        }

        public int getRownum() {
            return rownum;
        }

        public void setRownum(int rownum) {
            this.rownum = rownum;
        }

        public List<XlsColumn> getColumns() {
            return columns;
        }

        public void setColumns(List<XlsColumn> columns) {
            this.columns = columns;
        }

        public Row getRow() {
            return row;
        }

        @Override
        public String toString() {
            return "XlsRow{" + "rownum=" + rownum + ", columns=" + columns + '}';
        }

        @Override
        public int hashCode() {
            int hash = 7;
            return hash;
        }

        @Override
        public boolean equals(Object obj) {
            if (obj == null) {
                return false;
            }
            if (getClass() != obj.getClass()) {
                return false;
            }
            final XlsRow other = (XlsRow) obj;
            if (this.columns.size() > 0 && other.columns.size() > 0) {
                return this.columns.get(0).descrizione.equals(other.columns.get(0).descrizione);
            }
            return false;
        }

        private void writeRow(Sheet sheet, int r) {

            Row row = sheet.createRow(r);
            int c = 0;
            short[] colsIdx = new short[columns.size()];
            int i = 0;

            for (XlsColumn xlsColumn : columns) {
                colsIdx[i] = (short) xlsColumn.colNum;
                Cell _cell = row.createCell(colsIdx[i]);

                int rowIndex = xlsColumn.rowIndex;
                for (XlsMergeRange xlsMergeRange : mergeRanges) {
                    if (rowIndex == xlsMergeRange.firstRow && rowIndex == xlsMergeRange.lastRow) {
                        CellRangeAddress cra = new CellRangeAddress(r, r, xlsMergeRange.firstColumn, xlsMergeRange.lastColumn);
                        sheet.addMergedRegion(cra);
                    }
                }
                _cell.setCellValue(xlsColumn.descrizione);

                i++;
            }

        }

    }

    private static class XlsColumn {

        private Cell cell;

        private final int rowIndex;

        public XlsColumn(Cell cell) {
            this.cell = cell;
            this.colNum = cell.getColumnIndex();
            this.descrizione = cell.getStringCellValue();
            this.rowIndex = cell.getRowIndex();
        }

        final int colNum;
        final String descrizione;

        public Cell getCell() {
            return cell;
        }

        public int getColNum() {
            return colNum;
        }

        public String getDescrizione() {
            return descrizione;
        }

        @Override
        public String toString() {
            return "XlsColumn{" + "colNum=" + colNum + ", descrizione=" + descrizione + '}';
        }
    }

    private static class XlsMergeRange {

        private final int firstRow;
        private final int lastRow;
        private final int firstColumn;
        private final int lastColumn;
        private String rowRange;
        private String columnRange;

        private final boolean isFullRowRange;
        private final boolean isFullColumnRange;

        public XlsMergeRange(CellRangeAddress cra) {
            this.firstRow = cra.getFirstRow();
            this.lastRow = cra.getLastRow();
            this.firstColumn = cra.getFirstColumn();
            this.lastColumn = cra.getLastColumn();
            this.rowRange = new StringBuilder(Integer.toString(firstRow)).append("-").append(Integer.toString(lastRow)).toString();
            this.columnRange = new StringBuilder(Integer.toString(firstRow)).append("-").append(Integer.toString(lastRow)).toString();
            this.isFullRowRange = cra.isFullRowRange();
            this.isFullColumnRange = cra.isFullColumnRange();

        }

        @Override
        public int hashCode() {
            int hash = 3;
            return this.firstRow * this.lastRow * this.firstColumn * this.lastColumn;
        }

        @Override
        public boolean equals(Object obj) {
            if (obj == null) {
                return false;
            }
            if (getClass() != obj.getClass()) {
                return false;
            }
            final XlsMergeRange other = (XlsMergeRange) obj;
            if (this.firstRow != other.firstRow) {
                return false;
            }
            if (this.lastRow != other.lastRow) {
                return false;
            }
            if (this.firstColumn != other.firstColumn) {
                return false;
            }
            if (this.lastColumn != other.lastColumn) {
                return false;
            }

            return rowRange.equals(other.rowRange) && columnRange.equals(other.columnRange);

        }

    }

    private static class CellRange {

        private int start;
        private int end;

        public CellRange(int start, int end) {
            this.start = start;
            this.end = end;
        }

        public boolean isBetween(int cellnum) {
            if (end == start) {
                return false;
            }
            return cellnum <= end && cellnum >= start;
        }

        public int getStart() {
            return start;
        }

        public void setStart(int start) {
            this.start = start;
        }

        public int getEnd() {
            return end;
        }

        public void setEnd(int end) {
            this.end = end;
        }

        @Override
        public int hashCode() {
            return start;
        }

    }

}
